package com.sapient;

import java.io.IOException;
import java.sql.*;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class CurrencyEdit
 */
@WebServlet("/CurrencyEdit")
public class CurrencyEdit extends HttpServlet {
	String[] result = null;
	String[] IsFlag = null;
	String[] details = null;

	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public CurrencyEdit() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int rows = 0;
		String selected = request.getParameter("selected");
		details = null;
		ResultSet rs = null;
		Connection conn = null;
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("role");
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			String SQL = "";
			
			if(user.equals("user"))
				SQL = "select currencyNAME from Currency where flag='N'";
			else
				SQL = "select currencyNAME from Currency";
			rs = st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}

			rs= st.executeQuery(SQL);

			result = new String[rows];
			int count = 0;
			while(rs.next())
			{
				result[count] = rs.getString(1);
				count++; 
			}

			if(selected!=null){
				details = new String[3];
				SQL = "select CurrencyNAME, description, flag from Currency where currencyname = '"+selected+"'";
				rs=st.executeQuery(SQL);
				while(rs.next())
				{
					details[0] = rs.getString(1);
					details[1] = rs.getString(2);
					details[2] = rs.getString(3);
				}
				
			}
			
			
			
			conn.close(); 
		}catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		}
		request.setAttribute("currency", result);
		request.setAttribute("Curdetails", details);
		request.setAttribute("Flag", IsFlag);
		RequestDispatcher r = getServletContext().getRequestDispatcher("/CurrencyEdit.jsp");
		r.forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String currencyName = request.getParameter("CurrencyName");
		String description = request.getParameter("Description");
		String SelectFlag = request.getParameter("SelectFlag");
		String oldFlag=request.getParameter("oldFlag");
		details = new String[4];
		details[0]=currencyName;
		details[1]=description;
		details[2]=SelectFlag;
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("User");
		String status = "";

		Connection conn = null;
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			
			String currencyFlag = "";
			String locationFlag = "";
			String marketFlag = "";
			if(!oldFlag.equals(SelectFlag)){
				if(SelectFlag.equals("N")){ //Yes to No
					String SQL1 = "select commoditytype,location,marketflag,marketid from market where currency = '"+currencyName+"'";
					ResultSet rs = st.executeQuery(SQL1);
					String[][] mlist = null;
					int rows=0;
					while(rs.next()){
						rows++;
					}
					rs= st.executeQuery(SQL1);
					mlist = new String[rows][8];
					int count = 0;
					while(rs.next())
					{
						mlist[count][0] = rs.getString(1);//c
						mlist[count][1] = rs.getString(2);//l
						mlist[count][2] = rs.getString(3);//mflag
						mlist[count][3] = rs.getString(4);//mid
						count++; 
						System.out.println(rs.getString(1)+rs.getString(2)+rs.getString(3)+rs.getString(4));
					}
					
					
					for(String[] s : mlist){
						String SQL2 = "select c.flag from commodity c where c.commoditytype = '"+s[0]+"'";
						System.out.println(SQL2);
						ResultSet rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							currencyFlag = rs2.getString(1); //currency flag
							System.out.println("Currency flag: "+currencyFlag);
						}
						SQL2 = "select l.flag from location l where l.locationname = '"+s[1]+"'";
						rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							locationFlag = rs2.getString(1); //location flag
							System.out.println("Location flag: "+locationFlag);
						}
							marketFlag = s[2]; //market flag
							System.out.println("Market flag: "+marketFlag);
							
						if(currencyFlag.equals("N")&&locationFlag.equals("N")&&marketFlag.equals("N")){
							SQL2="update Market set flag = 'N' where marketid = "+s[3];
							System.out.println(SQL2);
							st.executeUpdate(SQL2);
						}
					}
					System.out.println(count);
				}
				else{ 
					String SQL2 = "update Market set flag = 'Y' where currency = '"+currencyName+"'";
					System.out.println(SQL2);
					st.executeUpdate(SQL2);
				}
			}
			
			String SQL = "update Currency set  description = '"+description+
					"',lastupdateduser = '"+user+"',lastupdateddate=CURRENT_DATE, flag= '"+SelectFlag+"' where currencyname = '"+currencyName+"'";
			st.executeUpdate(SQL);
			status = "Successfully updated!";

			conn.close(); 
		} catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		} 
		request.setAttribute("status", status);
		request.setAttribute("currency", result);
		request.setAttribute("Curdetails", details);
		RequestDispatcher r = getServletContext().getRequestDispatcher("/CurrencyEdit.jsp");
		r.forward(request, response);
	}

}
